﻿CREATE PROCEDURE [usix].[s_CustomerFile_Update_FullInsert]
AS
DECLARE @Today datetime SET @Today = dbo.Today(GETDATE())
SELECT COALESCE(L_Bs.LoanID,[Open Field#01],0)ApplID, L_Bs.LoanID, DM_EXP.[Customer #] UsixID, DM_EXP.[Customer #], CONVERT(int,DM_EXP.[Open Field#01])[Open Field#01], ISNULL(B.SSN, DM_EXP.[Primary SSN]) AS SSN, ISNULL(CI.VIN, 
LEFT(DM_EXP.Serial#, 17)) AS VIN, DM_EXP.Serial#, S.ID AS StatusID, DM_EXP.[G_L Date],DM_EXP.[Open Date], DM_EXP.Name, DM_EXP.Address, DM_EXP.City, 
DM_EXP.St, DM_EXP.[Zip Code], DM_EXP.[Home Phone #], DM_EXP.[Work Phone #], DM_EXP.[Car Year], DM_EXP.Make, DM_EXP.Model, 
DM_EXP.[Pymnt Schedule], DM_EXP.[# of Payments], DM_EXP.[# of PaymentsM], DM_EXP.[Payment Amount], DM_EXP.[Total Payments], DM_EXP.[1st_ Payment Date], 
DM_EXP.[Last Payment Date], DM_EXP.[Next Payment Date], DM_EXP.[# Pmts Remaining], DM_EXP.[# Pmts RemainingM], DM_EXP.[Balance Remaining], 
DM_EXP.[Total Sales Price], DM_EXP.[Finance Charges], DM_EXP.[Status Code], DM_EXP.P_O_E_, DM_EXP.Loan#_Comments, 
DM_EXP.[Primary SSN], DM_EXP.[Amt_ Fin__Disc_ Amt], DM_EXP.[Interest Income], DM_EXP.[Earned Discount], DM_EXP.[Dealer Discount], 
DM_EXP.[Dealer Proceed], DM_EXP.[Dealer Code], DM_EXP.[Principal Balance], DM_EXP.APR, DM_EXP.Mileage, DM_EXP.[Collector Id#], 
DM_EXP.[Discount%], DM_EXP.[Origination State Code], DM_EXP.Portfolio#, DM_EXP.[Sales Rep_], DM_EXP.[Investor Client], DM_EXP.[Date L_C], 
DM_EXP.[Joint Name], DM_EXP.[Joint Address], DM_EXP.[Joint City], DM_EXP.[Joint State], DM_EXP.[Joint Zipcode], DM_EXP.[Joint SSN],
DM_EXP.[Joint H Phone#], DM_EXP.[Joint W Phone#], DM_EXP.[Primary Cell#], DM_EXP.[Joint Cell#], DM_EXP.[Partial Payment], 
DM_EXP.[NSF Balance], DM_EXP.[Late Chg Bal], DM_EXP.[Apply Late Fee], DM_EXP.Branch#, DM_EXP.CompanyID, 
usix.Bankruptcy_Y_N(DM_EXP.[Bankruptcy Filed Y_N?])[Bankruptcy Filed Y_N?],
DM_EXP.[Bankruptcy Type 07_13?], DM_EXP.CompanyName, DM_EXP.[Total Principal],
DM_EXP.DPD, 
DM_EXP.DPD0, 
DM_EXP.DSLP, DM_EXP.PaymentsMade , DM_EXP.PaymentsMadeM, DM_EXP.DownPayment, DM_EXP.[Princ/ChOff Ballance], DM_EXP.[Repo Date], DM_EXP.[A_R Balance], 
DM_EXP.[Resale Amt_], DM_EXP.Chargeoff, DM_EXP.[Final Repo Date], DM_EXP.Retainage, DM_EXP.[Other Chgs Bal],DM_EXP.[Title Received Y_N?],
DM_EXP.NDay, DM_EXP.[1st_ Payment Date] FirstPaymentDateCalc,
CASE WHEN DM_EXP.[1st_ Payment Date] < getdate() - 1
		 THEN dbo.MinNum(dbo.MonthsApart(DM_EXP.[1st_ Payment Date] , GETDATE()-1) + 1 , DM_EXP.[# of PaymentsM])
		 ELSE 0 END  PaymentsExpectedM,
DM_EXP.CompanyID_Raw,
DM_EXP.DPD AS DPDCalc,
DM_EXP.[Simple_Add-On],
0 VINCheck,
LPs.Charge LateCharges,
LPs.Periods+CASE WHEN LPs.OddDays > 0 THEN 1 ELSE 0 END LatePmtsCount,
CASE [Simple_Add-On] WHEN 'S1' THEN 0 ELSE dbo.Payoff78([Amt_ Fin__Disc_ Amt],[Payment Amount],[# Pmts Remaining],[# of Payments])END RemBalance78,
PerDiem
,dbo.clr_RegEx_MatchGroup(DM_EXP.Name,'(^[^,]+)',1)NameLast
,dbo.clr_RegEx_MatchGroup(DM_EXP.Name,'^[^,]+,\s(.+)',1)NameFirst
,DM_EXP.PaymentsMadeM/*
dbo.PmtsAdjMos(
	dbo.PaymentMadeByPrincipalBalance(DM_EXP.[Amt_ Fin__Disc_ Amt],DM_EXP.[Payment Amount],DM_EXP.[Open Date],DM_EXP.[1st_ Payment Date],[# of Payments],PS.Frequency,DM_EXP.APR,0,DM_EXP.[Total Principal])
	,PS.Frequency,0
)*/ PaymentsMadeByPrinBal
,PaymentAmountM
INTO #CustomerFile_Export
FROM t_Loan_Borrower AS L_Bs
INNER JOIN t_Loan AS L ON L.ID = L_Bs.LoanID
INNER JOIN t_State AS S ON L.StatusID = S.ID
INNER JOIN t_CarInfo AS CI ON L_Bs.LoanID = CI.LoanID
INNER JOIN t_Borrower AS B ON L_Bs.BorrowerID = B.ID AND L_Bs.Pos = 0
FULL OUTER JOIN DataMining.dbo.t_All_Cust_Loan_Repo_PVT AS DM_EXP
	ON B.SSN = DM_EXP.[Primary SSN] AND (CI.VIN_L9 = DM_EXP.VIN_L9 OR
		 CI.VIN_8R = DM_EXP.VIN_8R) OR
		 CI.LoanID = DM_EXP.[Open Field#01]
LEFT OUTER JOIN ts_LoanPaymentType LPT ON LPT.Code = DM_EXP.[Pymnt Schedule]
OUTER APPLY dbo.LateChargeInfo(DM_EXP.[Total Principal],
	DM_EXP.[Payment Amount],LPT.PaymentsPerYear,DM_EXP.[Next Payment Date],GETDATE(),DM_EXP.[Late Days],DM_EXP.[Late Chg%],DM_EXP.[Late Chg Max]
)LPs
INNER JOIN usix.t_PaymentSchedule PS ON PS.PS = DM_EXP.[Pymnt Schedule]
IF @@ERROR = 0 BEGIN

BEGIN TRAN
TRUNCATE TABLE usix.t_CustomerFile_Export
INSERT INTO usix.t_CustomerFile_Export
SELECT CF_E.* FROM #CustomerFile_Export CF_E
INNER JOIN (SELECT     SSN, VIN, MAX([G_L Date]) AS [G_L Date] FROM #CustomerFile_Export GROUP BY SSN, VIN)CF_U
	ON CF_E.SSN = CF_U.SSN AND CF_E.VIN = CF_U.VIN AND CF_E.[G_L Date] = CF_U.[G_L Date]
COMMIT 

END
DROP TABLE #CustomerFile_Export


